package edu.ahpu.propertymanage.serviceimpl;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import edu.ahpu.propertymanage.service.ExcelServer;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

@Service
public class ExcelServerImpl implements ExcelServer {

    static SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    static SimpleDateFormat ssf = new SimpleDateFormat("yyyy-MM-dd");


    @Override
    public SXSSFWorkbook writerExcel(LinkedHashMap titleMap, List<?> data) throws ParseException {
        //数据预处理
        //excel表头对应的value
        List<String> titleName = new ArrayList<String>(titleMap.values());

        //excel表头对应的key
        List<String> titleKey = new ArrayList<String>(titleMap.keySet());

        //创建一个excel对象
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        //生成一个excel表格
        SXSSFSheet sheet = workbook.createSheet();

        //创建第一行
        SXSSFRow row = sheet.createRow(0);
        //写入第一行信息
        for (int i = 0; i < titleName.size(); i++) {
            sheet.setColumnWidth(i, 20 * 256);
            row.createCell(i).setCellValue(titleName.get(i));
        }
        int count = 0;
        for (int k = 0; k < data.size(); k++) {
            count++;
            row = sheet.createRow(count);
            Map map = JSONObject.parseObject(JSONObject.toJSONString(data.get(k)), Map.class);
            for (int i = 0; i < titleKey.size(); i++) {

                Object objectValue = map.get(titleKey.get(i));
                if (objectValue == null)
                    objectValue = "";
                String objectVal = "";
                SXSSFCell cell = row.createCell(i);
                    switch (titleKey.get(i)) {
                        case "visitorArrTime":
                            objectVal = sf.format(objectValue);
                            break;
                        case "lastMonthWaterTime":
                            objectVal = ssf.format(objectValue);
                            break;
                        case "thisMonthWaterTime":
                            objectVal = ssf.format(objectValue);
                            break;
                        case "lastMonthEleTime":
                            objectVal = ssf.format(objectValue);
                            break;
                        case "thisMonthEleTime":
                            objectVal = ssf.format(objectValue);
                            break;
                        case "lastMonthGasTime":
                            objectVal = ssf.format(objectValue);
                            break;
                        case "thisMonthGasTime":
                            objectVal = ssf.format(objectValue);
                            break;
                        default:
                            objectVal = objectValue.toString();
                            break;
                    }
                cell.setCellValue(objectVal);
            }
        }
        count = 0;

        return workbook;

    }

    @Override
    public List postExcel(InputStream fileInputStream, LinkedHashMap titleMap) {
        //返回值
        List<Object> objectList = new ArrayList<>();
        //Map中转
        Map<String, String> oMap = new LinkedHashMap<>();
        //拿到一个excel对象
        XSSFWorkbook workbook = null;
        try {
            workbook = new XSSFWorkbook(fileInputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
        //拿到一个sheet对象
        XSSFSheet sheet = workbook.getSheetAt(0);
        //获取总行数
        int rowCount = sheet.getLastRowNum();
        //拿到第一行
        XSSFRow row = sheet.getRow(0);
        //获取总列数
        int cellCount = row.getLastCellNum();
        //获取对应的Key值
        List<String> titleKey = new ArrayList<>(titleMap.keySet());

        //设置一行cell的数据
        List<String> valueList = new ArrayList<>();
        //获取数据
        for (int k = 1; k <= rowCount; k++) {
            row = sheet.getRow(k);
            //获取一个cell中的数据
            for (int i = 0; i < cellCount; i++) {
                if (row.getCell(i) == null)
                    row.createCell(i).setCellValue("");
                row.getCell(i).setCellType(CellType.STRING);
                String value = row.getCell(i).getStringCellValue();
                valueList.add(value);
            }
            //将没一行的cell值填入map形成对象
            for (int i = 0; i < cellCount; i++) {
                oMap.put(titleKey.get(i), valueList.get(i));
            }

            //将Map转为对应的Object
            Object tObjtce = JSONObject.parseObject(JSON.toJSONString(oMap), Object.class);
            objectList.add(tObjtce);
            valueList.clear();
        }

        return objectList;
    }
}
